﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Data.OleDb;

namespace template_dll
{
    public partial class Form2 : Form
    {
        public Form2()
        {
            InitializeComponent();
        }

        public DataTable[] dt_site = new DataTable[0];
        public DataTable dt_drugs = new DataTable();
        public SqlConnection conn;
        public SqlConnection con_pharmolapdw;
        private struct sp_params
        {
            public string name;
            public object value;
        }
        private sp_params[] cb_currentitem = new sp_params[0];
        private int drugcode = 0;

        private void Form2_Load(object sender, EventArgs e)
        {
            if (dt_site[0].Rows.Count > 0)
            {
                NewItem[] newitem = new NewItem[0];
                foreach (DataRow row in dt_site[0].Rows)
                {
                    Array.Resize(ref newitem, newitem.Length + 1);
                    newitem[newitem.Length - 1] = new NewItem();
                    newitem[newitem.Length - 1].ParamDirectoryID = Convert.ToInt32(row[0]);
                    newitem[newitem.Length - 1].ParamComment = row[1].ToString();
                    newitem[newitem.Length - 1].ParentID = Convert.ToInt32(row[3]);
                    comboBox1.Items.Add(newitem[newitem.Length - 1]);
                }
            }
        }

        private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
        {
            if ((sender as ComboBox).SelectedIndex != -1)
            {
                try
                {
                    DataTable[] dt_rep = SiteParamsSel(conn, (comboBox1.SelectedItem as NewItem).ParamDirectoryID);

                    Control[] control = new Control[0];

                    for (int i = 0; i < comboBox1.Parent.Controls.Count; i++)
                    {
                        if (comboBox1.Parent.Controls[i].Name != "comboBox1"
                            && comboBox1.Parent.Controls[i].GetType().ToString() == @"System.Windows.Forms.ComboBox")
                        {
                            Array.Resize(ref control, control.Length + 1);
                            control[control.Length - 1] = comboBox1.Parent.Controls[i];
                        }
                    }
                    foreach (Control c in control)
                    {
                        comboBox1.Parent.Controls.Remove(c);
                    }
                    ComboBoxAddItems(dt_rep);
                    int countcb = 0;
                    for (int i = 0; i < comboBox1.Parent.Controls.Count; i++)
                    {
                        if (comboBox1.Parent.Controls[i].Name != "comboBox1"
                            && comboBox1.Parent.Controls[i].GetType().ToString() == @"System.Windows.Forms.ComboBox")
                            countcb++;
                    }
                    dataGridView1.DataSource = null;
                    dt_drugs = null;
                    if (countcb == 0)
                    {
                        dt_drugs = DrugsSel((comboBox1.SelectedItem as NewItem).ParamDirectoryID,null);
                        dataGridView1.DataSource = dt_drugs.DefaultView;
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
            }
        }

        private DataTable DrugsSel(object siteid,object parentid)
        {

            sp_params[] par = new sp_params[2];
            par[0].name = "@wsid";
            par[0].value = siteid;
            par[1].name = "@parentid";
            par[1].value = parentid;
            DataTable[] dt = SqlProcExecWithQuery(conn, "pas_DrugsSel", 60, par);
            if (dt.Length == 0)
            {
                dt = new DataTable[1];
                dt[0] = new DataTable();
            }
            return dt[0];
        }

        private DataTable[] SiteParamsSel(SqlConnection sqlcon, int value)
        {
            try
            {
                sp_params[] par = new sp_params[1];
                par[0].name = "@wsid";
                par[0].value = value;
                DataTable[] dt = SqlProcExecWithQuery(sqlcon, "pas_ParamForParent", 300, par);
                return dt;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                return null;
            }
        }

        private DataTable[] SqlProcExecWithQuery(SqlConnection con, string procname, int timeout, sp_params[] param)
        {
            DataTable[] dt = new DataTable[0];
            try
            {
                SqlCommand com = con.CreateCommand();
                com.CommandType = CommandType.StoredProcedure;
                com.CommandTimeout = timeout;
                com.CommandText = procname;

                if (param != null)
                {
                    foreach (sp_params p in param)
                    {
                        com.Parameters.AddWithValue(p.name, p.value);
                    }
                }
                SqlDataReader dr = com.ExecuteReader();

                while (dr.HasRows)
                {
                    Array.Resize(ref dt, dt.Length + 1);
                    dt[dt.Length - 1] = new DataTable();
                    dt[dt.Length - 1].Load(dr);
                    if (dt[dt.Length - 1].Columns.Count > 2) dt[dt.Length - 1].TableName = dt[dt.Length - 1].Rows[0][1].ToString();
                    if (dr.IsClosed) break;
                }

                dr.Close();

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            return dt;
        }

        private void ComboBoxAddItems(DataTable[] dt)
        {
            DataRow r;
            DataTable tmp = new DataTable();
            Point point = new Point(comboBox1.Location.X, comboBox1.Location.Y + comboBox1.Height + 10);
            try
            {
                cb_currentitem = null;
                cb_currentitem = new sp_params[0];
                foreach (DataTable table in dt)
                {
                    ComboBox cb = ComboBoxAdd(point, table.Rows[0][1].ToString(), groupBox1);
                    cb.SelectedIndexChanged += new System.EventHandler(newComboBox_SelectedIndexChanged);

                    Array.Resize(ref cb_currentitem, cb_currentitem.Length + 1);
                    cb_currentitem[cb_currentitem.Length - 1].name = table.Rows[0][1].ToString();
                    cb_currentitem[cb_currentitem.Length - 1].value = -1;

                    point.Y += comboBox1.Height + 10;
                    NewItem[] newitem = new NewItem[1];
                    newitem[0] = new NewItem();
                    newitem[0].ParamDirectoryID = -1;
                    newitem[0].ParamName = table.Rows[0][1].ToString();
                    newitem[0].ParamValue = "-1";
                    newitem[0].ParamComment = "<новая запись>";
                    newitem[0].ParentID = Convert.ToInt32(table.Rows[0][0]);

                    cb.Items.Add(newitem[0]);

                    foreach (DataRow row in table.Rows)
                    {
                        r = row;
                        Array.Resize(ref newitem, newitem.Length + 1);
                        newitem[newitem.Length - 1] = new NewItem();
                        newitem[newitem.Length - 1].ParamDirectoryID = Convert.ToInt32(row[2]);
                        newitem[newitem.Length - 1].ParamName = row[1].ToString();
                        newitem[newitem.Length - 1].ParamValue = row[4].ToString();
                        newitem[newitem.Length - 1].ParamComment = row[3].ToString();
                        newitem[newitem.Length - 1].ParentID = Convert.ToInt32(row[0]);
                        cb.Items.Add(newitem[newitem.Length - 1]);
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            //comboBox1.Parent.Height = point.Y + 10;
        }

        private ComboBox ComboBoxAdd(Point location, string name, GroupBox gb)
        {
            ComboBox cb = new ComboBox();
            cb.Name = name;
            cb.Location = location;
            cb.Height = 21;
            cb.Width = 179;
            cb.Visible = true;
            gb.Controls.Add(cb);

            return cb;
        }

        private void newComboBox_SelectedIndexChanged(object sender, EventArgs e)
        {
            int indexold = -1;
            int indexnew = (sender as ComboBox).SelectedIndex;
            NewItem ni = ((sender as ComboBox).SelectedItem as NewItem);
            foreach (sp_params par in cb_currentitem)
                if (par.name == ni.ParamName) indexold = Convert.ToInt32(par.value);
            if (indexold != indexnew)
            {
                try
                {
                    if (ni.ParamComment == "<новая запись>")
                    {
                        Form3 f3 = new Form3();
                        f3.formtext = "\"" + ni.ParamName + "\" сайта " + comboBox1.Text;
                        if (f3.ShowDialog() == DialogResult.OK)
                        {
                            sp_params[] par = new sp_params[5];
                            par[0].name = "@parid";
                            par[0].value = ni.ParentID;
                            par[1].name = "@parentid";
                            par[1].value = null;
                            par[2].name = "@parval";
                            par[2].value = f3.paramvalue;
                            par[3].name = "@parcom";
                            par[3].value = f3.paramcomment;
                            par[4].name = "@pdid";
                            par[4].value = null;
                            SqlProcExecWithQuery(conn, "pas_ParamDirectoryInsUpd", 30, par);
                            int cb1_ind = comboBox1.SelectedIndex;
                            comboBox1.SelectedIndex = -1;
                            comboBox1.SelectedIndex = cb1_ind;
                        }
                        else (sender as ComboBox).SelectedIndex = indexold;
                        f3.Close();
                        f3 = null;
                    }
                    else
                    {
                        dataGridView1.DataSource = null;
                        dt_drugs = DrugsSel((comboBox1.SelectedItem as NewItem).ParamDirectoryID, ni.ParamDirectoryID);
                        dataGridView1.DataSource = dt_drugs.DefaultView;
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                    (sender as ComboBox).SelectedIndex = indexold;
                }

            }
        }

        private void button1_Click(object sender, EventArgs e)
        {
            try
            {
                if (textBox1.Text != "")
                {
                    SqlCommand dr = con_pharmolapdw.CreateCommand();
                    dr.CommandType = CommandType.StoredProcedure;
                    dr.CommandText = "pas_AllDrugsSel";
                    dr.Parameters.Add("@drug", SqlDbType.NVarChar, 20);
                    dr.Parameters["@drug"].Value = textBox1.Text;
                    if (con_pharmolapdw.State == ConnectionState.Closed) con_pharmolapdw.Open();
                    DataTable dt = new DataTable();
                    dt.Load(dr.ExecuteReader());

                    Form4 f2 = new Form4();
                    f2.dataGridView1.DataSource = dt.DefaultView;
                    for (int i = 0; i < f2.dataGridView1.ColumnCount; i++)
                    {
                        if (i != 1 && i != 2) f2.dataGridView1.Columns[i].Visible = false;
                    }
                    f2.dataGridView1.Columns[2].Width = f2.dataGridView1.Width - f2.dataGridView1.Columns[1].Width;
                    f2.dataGridView1.Focus();
                    f2.ShowDialog();
                    textBox1.Text = f2.dataGridView1.CurrentRow.Cells[2].Value.ToString();
                    drugcode = Convert.ToInt32(f2.dataGridView1.CurrentRow.Cells[1].Value);
                    f2.Close();
                    f2 = null;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void textBox1_KeyDown(object sender, KeyEventArgs e)
        {
            if (e.KeyCode == Keys.Enter) button1_Click(sender, e);
        }

        private void button2_Click(object sender, EventArgs e)
        {
            try
            {
                bool flag = false;
                for (int i = 0; i < comboBox1.Parent.Controls.Count; i++)
                {
                    if (comboBox1.Parent.Controls[i].GetType().ToString() == @"System.Windows.Forms.ComboBox"
                        && (comboBox1.Parent.Controls[i] as ComboBox).SelectedIndex == -1)
                    {
                        MessageBox.Show("Не все параметры выбраны");
                        comboBox1.Parent.Controls[i].Focus();
                        flag = true;
                        break;
                    }
                }
                if (drugcode == 0 && !flag)
                {
                    textBox1.Focus();
                    flag = true;
                    MessageBox.Show("Не выбран товар МАП");
                }
                if (textBox2.Text == "" && !flag)
                {
                    textBox2.Focus();
                    flag = true;
                    MessageBox.Show("Не введен товар конкурента");
                }

                if (!flag)
                {
                    object parentid = null;
                    for (int i = 0; i < comboBox1.Parent.Controls.Count; i++)
                    {
                        if (comboBox1.Parent.Controls[i].Name != "comboBox1"
                            && comboBox1.Parent.Controls[i].GetType().ToString() == @"System.Windows.Forms.ComboBox")
                        {
                            parentid = ((comboBox1.Parent.Controls[i] as ComboBox).SelectedItem as NewItem).ParamDirectoryID;
                        }
                    }
                    sp_params[] par = new sp_params[5];
                    par[0].name = "@parid";
                    par[0].value = (comboBox1.SelectedItem as NewItem).ParentID;
                    par[1].name = "@parentid";
                    par[1].value = parentid;
                    par[2].name = "@parval";
                    par[2].value = textBox2.Text.Trim();
                    par[3].name = "@parcom";
                    par[3].value = "dc" + drugcode.ToString();
                    par[4].name = "@pdid";
                    par[4].value = null;
                    SqlProcExecWithQuery(conn, "pas_ParamDirectoryInsUpd", 30, par);

                    dataGridView1.DataSource = null;
                    dt_drugs = null;
                    dt_drugs = DrugsSel((comboBox1.SelectedItem as NewItem).ParamDirectoryID, parentid);
                    dataGridView1.DataSource = dt_drugs.DefaultView;

                    drugcode = 0;
                    textBox1.Text = "";
                    textBox2.Text = "";
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void button3_Click(object sender, EventArgs e)
        {
            try
            {
                if (dataGridView1.RowCount > 0)
                {
                    if (dataGridView1.CurrentCell.RowIndex > -1)
                    {
                        if (MessageBox.Show(dataGridView1.CurrentRow.Cells["Код МАП"].Value.ToString() + "\n" + dataGridView1.CurrentRow.Cells["Товар конкурента"].Value.ToString(), "Удалить строку?", MessageBoxButtons.YesNo) == DialogResult.Yes)
                        {
                            sp_params[] par = new sp_params[1];
                            par[0].name = "@pdid";
                            par[0].value = dataGridView1.CurrentRow.Cells["ИД привязки"].Value;

                            SqlProcExecWithQuery(conn, "pas_ParamDirectoryDel", 30, par);
                            int ind = dataGridView1.CurrentRow.Index;
                            CurrencyManager cm = (CurrencyManager)dataGridView1.BindingContext[dataGridView1.DataSource];
                            cm.SuspendBinding();
                            
                            dataGridView1.Rows.RemoveAt(ind);
                            if(cm.Position > -1) cm.ResumeBinding();
                        }
                    }
                    else MessageBox.Show("Не выбрана строка");
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void button4_Click(object sender, EventArgs e)
        {
            try
            {
                if (dataGridView1.RowCount > 0)
                {
                    if (MessageBox.Show("Все строки будут удалены!", "Внимание!", MessageBoxButtons.YesNo, MessageBoxIcon.Warning) == DialogResult.Yes)
                    {
                        sp_params[] par = new sp_params[1];
                        par[0].name = "@pdid";
                        foreach (DataGridViewRow row in dataGridView1.Rows)
                        {
                            par[0].value = Convert.ToInt32(row.Cells["ИД привязки"].Value);
                            SqlProcExecWithQuery(conn, "pas_ParamDirectoryDel", 30, par);
                        }
                        dataGridView1.DataSource = null;
                        dt_drugs = null;
                        MessageBox.Show("Все записи удалены");
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void button5_Click(object sender, EventArgs e)
        {
            try
            {
                bool flag = false;
                for (int i = 0; i < comboBox1.Parent.Controls.Count; i++)
                {
                    if (comboBox1.Parent.Controls[i].GetType().ToString() == @"System.Windows.Forms.ComboBox"
                        && (comboBox1.Parent.Controls[i] as ComboBox).SelectedIndex == -1)
                    {
                        MessageBox.Show("Не все параметры выбраны");
                        comboBox1.Parent.Controls[i].Focus();
                        flag = true;
                        break;
                    }
                }

                if (!flag)
                {
                    MessageBox.Show("Файл Excel\nТолько одна страница\nНа странице два столбца:\n\t1)Код товара в МАП\n\t2)Наименование товара у конкурента\nУ столбцов обязательно должны быть имена", "Требования к файлу");
                    OpenFileDialog opf = new OpenFileDialog();
                    opf.Filter = "Файлы Excel (1997-2007)|*.XLSX;*.XLS";
                    if (opf.ShowDialog() == DialogResult.OK)
                    {
                        string filename = opf.FileName;
                        string ConStr = String.Format("Provider=Microsoft.ACE.OLEDB.12.0; Data Source={0}; Extended Properties=Excel 8.0;", filename);
                        DataSet ds = new DataSet("EXCEL");
                        OleDbConnection cn = new OleDbConnection(ConStr);

                        if (cn.State == ConnectionState.Closed) cn.Open();
                        DataTable schemaTable = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { }); //{ null, null, null, null }
                        string sheet1 = (string)schemaTable.Rows[0].ItemArray[2];
                        string select = String.Format("SELECT * FROM [{0}]", sheet1);
                        OleDbDataAdapter ad = new OleDbDataAdapter(select, cn);
                        ad.Fill(ds);
                        DataTable tb = ds.Tables[0];
                        cn.Close();

                        if (tb.Rows.Count > 0)
                        {
                            Form5 form = new Form5();
                            form.dt = tb;
                            if (form.ShowDialog() == DialogResult.OK)
                            {
                                object parentid = null;
                                for (int i = 0; i < comboBox1.Parent.Controls.Count; i++)
                                {
                                    if (comboBox1.Parent.Controls[i].Name != "comboBox1"
                                        && comboBox1.Parent.Controls[i].GetType().ToString() == @"System.Windows.Forms.ComboBox")
                                    {
                                        parentid = ((comboBox1.Parent.Controls[i] as ComboBox).SelectedItem as NewItem).ParamDirectoryID;
                                    }
                                }
                                sp_params[] par = new sp_params[5];
                                par[0].name = "@parid";
                                par[0].value = (comboBox1.SelectedItem as NewItem).ParentID;
                                par[1].name = "@parentid";
                                par[1].value = parentid;
                                par[2].name = "@parval";
                                par[3].name = "@parcom";
                                par[4].name = "@pdid";
                                par[4].value = null;

                                foreach (DataRow row in tb.Rows)
                                {
                                    par[2].value = row[1].ToString();
                                    par[3].value = "dc" + row[0].ToString();
                                    SqlProcExecWithQuery(conn, "pas_ParamDirectoryInsUpd", 30, par);
                                }

                                MessageBox.Show("Файл залит");

                                dataGridView1.DataSource = null;
                                dt_drugs = null;
                                dt_drugs = DrugsSel((comboBox1.SelectedItem as NewItem).ParamDirectoryID, parentid);
                                dataGridView1.DataSource = dt_drugs.DefaultView;
                                
                            }
                            form.Close();
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void dataGridView1_CellDoubleClick(object sender, DataGridViewCellEventArgs e)
        {
            if (dataGridView1.DataSource != null)
                if (dataGridView1.RowCount > 0)
                    if (e.ColumnIndex > -1 && e.RowIndex > -1)
                    {
                        EventArgs ea = new EventArgs();
                        button3_Click(sender, ea);
                    }
        }
    }
}
